import calendar
import datetime
import time

from pymysql import connect

from taobao.extension import Link_database
# from Django_API.TaobaoWeb_Project.taobao.extension import Link_database


def OeratingTake(user_id, store_name, start, check):
    """每日运行数据查询"""
    con, obj = Link_database()  # 链接数据库

    # 查询当天数据
    # sql = """select statDate from operating where statDate=%d and user=%d""" % (date, user_id)

    # 前三十天数据查询
    # sql = """select statDate,store_product_cou,payItemcun,browse_number,
    #     visitors,payment,paymentRate,successful_refund,buyers,old_buyers,
    #     Unit_price,payment_Number,add_pay_number,Refund,Item_cnt
    #     from operating where user=%d AND  store_name='%s' order by
    #         statDate desc limit 30""" % (user_id, store_name)

    sql = """select statDate,store_product_cou,payItemcun,browse_number,
            visitors,payment,paymentRate,successful_refund,buyers,old_buyers,
            Unit_price,payment_Number,add_pay_number,Refund,Item_cnt
            from operating where user=%d and store_name='%s' and statDate between %d and %d""" \
          % (user_id, store_name, start, check)

    obj.execute(sql)  # 数据库查询
    query_data = obj.fetchall()  # 显示所有查询数据 数据格式为元祖
    obj.close()
    con.close()

    return query_data


# def TrafficTake(user_id, store_name, start, check):
#     """流量数据查询"""
#     con, obj = Link_database()  # 链接数据库
#
#     # 测试sql语句
#     sql = """select type,login,statdate,pagename,visitors,visitors_rate,order_buyers,
#             order_buyers_rate,order_conversion,order_conversion_rate from traffic where user=%d and store_name='%s' and
#             statdate between %d and %d order by statDate
#             """ % (user_id, store_name, start, check)
#
#     obj.execute(sql)  # 数据库查询
#     query_data = obj.fetchall()  # 显示所有查询数据 数据格式为元祖
#     obj.close()
#     con.close()
#
#     return query_data


class TrafficTake(object):
    """流量数据查询"""

    def __init__(self, user_id, store_name, start, check, source):
        self.user_id = user_id
        self.store_name = store_name
        self.start = start
        self.check = check
        self.source = source
        self.con, self.obj = Link_database()

    def select_data(self):
        """map无线数据"""
        # sql = """select type,login,statdate,pagename,visitors,visitors_rate,order_buyers,
        #             order_buyers_rate,order_conversion,order_conversion_rate from traffic where user=%d and store_name='%s' and login='WX' and pagename='%s' and
        #             statdate between %d and %d order by statDate
        #             """ % (self.user_id, self.store_name, self.source, self.start, self.check)

        sql = """select statdate,pagename,visitors,order_buyers,
                            order_conversion,unit_price,paynumber from traffic where user=%d and store_name='%s' and login='WX' and pagename='%s' and
                            statdate between %d and %d order by statDate
                            """ % (self.user_id, self.store_name, self.source, self.start, self.check)

        self.obj.execute(sql)  # 数据库查询
        query_data = self.obj.fetchall()  # 显示所有查询数据 数据格式为元祖
        return query_data

    def WX_table_data(self):
        """无线 table 数据"""
        # sql = """select pagename,sum(visitors),sum(order_buyers) from traffic where user=%d and store_name='%s' and login='WX'
        #           and statdate between %d and %d group by pagename order by sum(visitors) desc limit 10""" % (
        #     self.user_id, self.store_name, self.start, self.check)

        sql = """select pagename,sum(visitors),sum(order_buyers),sum(unit_price),sum(paynumber) from traffic where user=%d and store_name='%s' and login='WX' 
                          and statdate between %d and %d group by pagename order by sum(visitors) desc limit 10""" % (
            self.user_id, self.store_name, self.start, self.check)

        self.obj.execute(sql)
        query_data = self.obj.fetchall()
        return query_data

    def PC_table_data(self):
        """PC table 数据"""
        # sql = """select pagename,sum(visitors),sum(order_buyers) from traffic where user=%d and store_name='%s' and login='PC'
        #                   and statdate between %d and %d group by pagename order by sum(visitors) desc limit 10""" % (
        #     self.user_id, self.store_name, self.start, self.check)

        sql = """select pagename,sum(visitors),sum(order_buyers),sum(unit_price),sum(paynumber) from traffic where user=%d and store_name='%s' and login='PC' 
                                  and statdate between %d and %d group by pagename order by sum(visitors) desc limit 10""" % (
            self.user_id, self.store_name, self.start, self.check)

        self.obj.execute(sql)
        query_data = self.obj.fetchall()
        return query_data

    def the_end(self):
        self.obj.close()
        self.con.close()


# def ItemAnalysis(user_id, store_name, start, check):
#     """单品数据分析数据查询"""
#     con, obj = Link_database()  # 链接数据库
#
#     sql = """select statdate,goods_id,visitors,pay_conrersion_rate,pay_amount,purchased_numder,
# access_age_amount,collection_numder,unit_price,search_pay_rate,search_visitors from itemanalysis
# where user=%d and store_name='%s' and statdate between %d and %d order by statDate""" % (user_id, store_name, start, check)
#
#     obj.execute(sql)
#     query_data = obj.fetchall()
#     obj.close()
#     con.close()
#
#     return query_data


class ItemAnalysis(object):
    """单品数据分析数据查询"""

    def __init__(self, user_id, store_name, start, check, goods_id):
        self.user_id = user_id
        self.store_name = store_name
        self.start = start
        self.check = check
        self.goods_id = goods_id
        self.con, self.obj = Link_database()
        # self.goods_id_set = goods_id_set

    def select_associatcd_num(self):
        """查询关联表 商品数"""
        sql = """select user_id,count(goods_id) from item_associated where user_id=%d and store_name='%s' group by user_id""" % (
            self.user_id, self.store_name)

        self.obj.execute(sql)
        num = self.obj.fetchone()
        return num

    def select_associatcd_table(self):
        """查询关联表 商品"""
        # sql = """select user_id,count(goods_id) from item_associated where user_id=%d and store_name='%s' and goods_id=%d group by user_id""" % (
        # self.user_id, self.store_name, self.goods_id)

        sql = """select goods_id from item_associated where user_id=%d and store_name='%s' group by goods_id""" % (
            self.user_id, self.store_name)

        self.obj.execute(sql)
        num = self.obj.fetchall()
        return num

    def select_goods_id(self):
        """查询商品 id"""
        # print(self.goods_id)
        sql = """select goods_id from item_associated where user_id=%d and store_name='%s' and goods_id=%d""" % (
            self.user_id, self.store_name, self.goods_id)

        self.obj.execute(sql)
        num = self.obj.fetchall()
        return num

    def save_associatcd_table(self):
        """保存关联表"""
        sql = """insert into item_associated values (0, %d, %d, '%s')""" % (
            self.user_id, self.goods_id, self.store_name)

        self.obj.execute(sql)
        self.con.commit()

    # def select_data(self, for_goods_id):
    #     """查询商品数据 map"""
    #
    #     # sql = """select statdate,goods_id,visitors,pay_conrersion_rate,pay_amount,purchased_numder,
    #     # access_age_amount,collection_numder,unit_price,search_pay_rate,search_visitors,title from itemanalysis
    #     # where user={} and store_name='{}' and goods_id in {} and statdate between {} and {} order by statDate"""\
    #     #     .format(self.user_id, self.store_name, goods_id_set, self.start, self.check)
    #
    #     sql = """select statdate,goods_id,visitors,pay_conrersion_rate,pay_amount,purchased_numder,
    #             access_age_amount,collection_numder,unit_price,search_pay_rate,search_visitors,title from itemanalysis
    #             where user={} and store_name='{}' and goods_id={} and statdate between {} and {}""" \
    #         .format(self.user_id, self.store_name, for_goods_id, self.start, self.check)
    #
    #     self.obj.execute(sql)
    #     query_data = self.obj.fetchall()
    #     return query_data

    def select_data(self, for_goods_id):
        """查询商品数据 map"""

        sql = """select statdate,goods_id,visitors,pay_conrersion_rate,average_time,out_rate,pay_amount,
                          pay_buyers,pay_old_buyers,pay_a_number,purchased_numder,access_age_amount,collection_numder,
                          refund_amount,month_pay_a_number,title from itemanalysis
                where user={} and store_name='{}' and goods_id={} and statdate between {} and {}""" \
            .format(self.user_id, self.store_name, for_goods_id, self.start, self.check)

        self.obj.execute(sql)
        query_data = self.obj.fetchall()
        return query_data

    def select_sum_data(self, goods_id_set):
        """查询指定时间 数据总和"""
        sql = """select goods_id,sum(visitors),sum(pay_amount),sum(purchased_numder),sum(access_age_amount),
                  sum(collection_numder),sum(unit_price),sum(search_visitors),count(visitors),title,sum(search_pay_rate) from itemanalysis
                  where user={} and store_name='{}' and goods_id in {} and statdate between {} and {} group by goods_id""" \
            .format(self.user_id, self.store_name, goods_id_set, self.start, self.check)

        # sql = """select goods_id,sum(visitors),pay_amount,sum(purchased_numder),sum(access_age_amount),
        #                   sum(collection_numder),sum(unit_price),sum(search_visitors),count(visitors),title from itemanalysis
        #                   where user={} and store_name='{}' and goods_id in {} and statdate between {} and {} group by goods_id""" \
        #     .format(self.user_id, self.store_name, goods_id_set, self.start, self.check)

        self.obj.execute(sql)
        query_data = self.obj.fetchall()
        return query_data

    def delete_goods_id(self, c):
        """删除指定商品 id"""
        sql = """delete from item_associated where user_id=%d and store_name='%s' and goods_id=%d""" % (
            self.user_id, self.store_name, c)

        self.obj.execute(sql)
        self.con.commit()

    def the_end(self):
        self.obj.close()
        self.con.close()


class ItemKeywords(object):
    """单品关键词数据查询业务类"""

    def __init__(self, user_id, store_name, start, check, goods_id):
        self.user_id = user_id
        self.store_name = store_name
        self.start = start
        self.check = check
        self.goods_id = goods_id
        self.con, self.obj = Link_database()

    def select_associatcd_num(self):
        """查询关联表 商品数"""
        sql = """select user_id,count(goods_id) from keywords_associated where user_id=%d and store_name='%s' group by user_id""" % (
            self.user_id, self.store_name)

        self.obj.execute(sql)
        num = self.obj.fetchone()
        return num

    def select_associatcd_table(self):
        """查询关联表 商品"""
        sql = """select goods_id from keywords_associated where user_id=%d and store_name='%s'""" % (
            self.user_id, self.store_name)

        self.obj.execute(sql)
        num = self.obj.fetchall()
        return num

    def select_goods_id(self):
        """查询商品 id"""
        sql = """select goods_id from keywords_associated where user_id=%d and store_name='%s' and goods_id=%d""" % (
            self.user_id, self.store_name, self.goods_id)

        self.obj.execute(sql)
        num = self.obj.fetchall()

        return num

    def save_associatcd_table(self):
        """保存关联表"""
        sql = """insert into keywords_associated values (0, %d, %d, '%s')""" % (
            self.user_id, self.goods_id, self.store_name)

        self.obj.execute(sql)
        self.con.commit()

    def select_data(self):
        """查询商品数据"""
        sql = """select source,sum(visitors),sum(jump_number),sum(jump_out_number),sum(collection_numder),sum(purchased_numder),
                  sum(pay_buyers),sum(fans_pay_buyers),sum(collection_pay_buyers),sum(purchased_pay_buyers),sum(unit_price) 
                  from test_itemkeywords where user=%d and goods_id=%d and store_name='%s'and statdate between %d and %d group by source
                  order by sum(visitors) desc limit 100""" % (
            self.user_id, self.goods_id, self.store_name, self.start, self.check)

        self.obj.execute(sql)
        query_data = self.obj.fetchall()
        return query_data

    def delete_goods_id(self):
        """删除指定商品 id"""
        sql = """delete from keywords_associated where user_id=%d and store_name='%s' and goods_id=%d""" % (
            self.user_id, self.store_name, self.goods_id)

        self.obj.execute(sql)
        self.con.commit()

    def the_end(self):
        self.obj.close()
        self.con.close()


def ServiceIndicators(user_id, store_name, start, check):
    """服务指标数据查询"""
    con, obj = Link_database()  # 链接数据库

    sql = """select statdate,refund_rate,independent_refund_rate,disputes_refund_rate,intervention, complaints_rate,
              refund_age_length,refund_return_age_length,ranking from serviceindicators
              where user=%d and store_name='%s' and statdate between %d and %d""" \
          % (user_id, store_name, start, check)

    obj.execute(sql)
    query_data = obj.fetchall()
    obj.close()
    con.close()

    return query_data


def SelectKey(user_id, store_name, goods_id, keywords, start, check):
    """查询关键词每天具体数据"""
    con, obj = Link_database()  # 链接数据库

    sql = """select statdate,visitors,jump_number,jump_out_number,collection_numder,purchased_numder,pay_buyers,fans_pay_buyers,collection_pay_buyers,purchased_pay_buyers,unit_price 
              from test_itemkeywords where user=%d and store_name='%s' and goods_id=%d and source='%s' and statdate
              between %d and %d""" % (user_id, store_name, goods_id, keywords, start, check)

    # sql = """select statdate,visitors,collection_numder,purchased_numder,pay_buyers_numder,pay_conrersion_rate
    #               from itemkeywords where user=%d and source='%s' and statdate
    #               between %d and %d""" % (user_id, keywords, start, check)

    obj.execute(sql)
    query_data = obj.fetchall()
    obj.close()
    con.close()

    return query_data


class QueryBBS(object):
    """论坛查询类"""

    def __init__(self):
        self.con, self.obj = Link_database()

    def QueryAllBBS(self):
        """查询所有论坛文章"""

        sql = """select title,article_simple,cover_photo,praise_number,published_date,published_name,id from dry_cargo_bbs"""
        self.obj.execute(sql)
        query_data = self.obj.fetchall()

        return query_data

    def DetailsBBS(self, id):
        """查询文章详情"""
        sql = """select title,content,published_date,published_name,article_simple from dry_cargo_bbs where id=%d""" % id
        self.obj.execute(sql)
        query_data = self.obj.fetchone()

        return query_data

    def UpdateSelect(self, id):
        """查询修改文章"""
        sql = """select title,article_simple,cover_photo,content,published_name,id from dry_cargo_bbs where id=%d""" % id
        self.obj.execute(sql)
        query_data = self.obj.fetchone()

        return query_data

    def GiveLike(self, id):
        """查询点赞数"""
        sql = """select praise_number from dry_cargo_bbs where id=%d""" % id
        self.obj.execute(sql)
        num = self.obj.fetchone()

        return num

    def the_end(self):
        self.obj.close()
        self.con.close()


# class Category(object):
#     """类目查询"""
#     def __init__(self, user_id, store_name, date):
#         self.user_id = user_id
#         self.store_name = store_name
#         self.date = date
#         self.con, self.obj = Link_database()
#
#     def WhenMonth(self):
#         """查询指定月时间"""
#         sql = """select fname,catename,payamt,payamtrate,paybuyercnt,uvvarirate from category_structure where user_id=%d
#                 and store_name='%s' and statdate=%d""" % (self.user_id, self.store_name, self.date)


def When_Month(user_id, date):
    """查询指定月时间"""
    con, obj = Link_database()

    date = int(time.mktime(time.strptime(date, '%Y-%m')))

    sql = """select statdate,fname,catename,payamt,payamtrate,paybuyercnt,payrate from category_structure where user_id=%d 
            and statdate=%d""" % (user_id, date)

    obj.execute(sql)
    query_data = obj.fetchall()

    con.close()
    obj.close()

    return query_data


def Before_Month(user_id, fname, catename, start, check):
    """类目之前数据查询"""
    con, obj = Link_database()

    sql = """select statdate,fname,catename,payamt,payamtrate,paybuyercnt,payrate from category_structure where user_id=%d 
                and fname='%s' and catename='%s' and statdate between %d and %d""" % (
    user_id, fname, catename, start, check)

    obj.execute(sql)
    query_data = obj.fetchall()

    con.close()
    obj.close()

    return query_data


class Admin_after(object):
    """后台管理查询类"""
    def __init__(self):
        self.con, self.obj = Link_database()

    def all_data(self):
        """查询所有用户数据"""
        # sql = """select username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id"""
        # sql = """select username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where id=98"""
        sql = """select count(id) from auth_user"""

        self.obj.execute(sql)
        query_data = self.obj.fetchone()[0]

        return query_data

    def all_page_data(self, page):
        """查询所有用户数据"""
        # sql = """select username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id order by id desc limit {},100""".format(page)
        sql = """select a.*,b.zd_nickname,b.zd_passwd,b.pay_date,b.is_diagnosis,b.landing_times from user_expand as b right join (select id,username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id order by auth_user.id desc limit {},25) as a on b.user_name=a.username""".format(
            page)

        self.obj.execute(sql)
        query_data = self.obj.fetchall()

        return query_data

    def not_payment(self):
        """查询未付款用户数据"""
        sql = """select username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where vip_level=1 or vip_level is Null"""
        # sql = """select username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where id=315"""

        self.obj.execute(sql)
        query_data = self.obj.fetchall()

        return query_data

    def will_due_to(self):
        """查询将到期用户数据"""
        start = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        check = (datetime.datetime.now() + datetime.timedelta(days=10)).strftime("%Y-%m-%d %H:%M:%S")
        # print(start)
        # print(check)

        sql = """select username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where vip_date between '%s' and '%s'""" % (start, check)
        # sql = """select username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where id=315"""

        self.obj.execute(sql)
        query_data = self.obj.fetchall()

        return query_data

    def due_to(self):
        """查询到期用户数据"""
        check = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        # print(check)

        sql = """select username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where vip_date between '2018-07-18 10:54:58' and '%s' order by vip_date desc""" % (check)
        # sql = """select username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id order by vip_date"""

        self.obj.execute(sql)
        query_data = self.obj.fetchall()

        return query_data

    def vip_level_num(self, level):
        """查询vip等级人数"""
        sql = """select count(id) from auth_user where vip_level='{}'""".format(level)

        self.obj.execute(sql)
        q = self.obj.fetchone()

        if q:
            q = q[0]
            return q

    def vip_level(self, page, level):
        """查询vip等级用户数据"""

        # sql = """select username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where vip_level='%s'""" % (level)
        # sql = """select a.*,b.zd_nickname,b.zd_passwd,b.pay_date,b.is_diagnosis,b.landing_times from user_expand as b right join (select id,username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id order by auth_user.id desc limit {},100) as a on b.user_name=a.username""".format(
        #     self.page, level)
        sql = """select a.*, b.zd_nickname,b.zd_passwd,b.pay_date,b.is_diagnosis,b.landing_times from user_expand as b right join (select id,username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where auth_user.vip_level='{}' order by auth_user.id desc limit {},25) as a on b.user_name=a.username""".format(level, page)

        self.obj.execute(sql)
        query_data = self.obj.fetchall()

        return query_data

    def select_username(self, username):
        """用户名查询用户数据"""

        # sql = """select username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where username='%s'""" % (username)
        sql = """select a.*, b.zd_nickname,b.zd_passwd,b.pay_date,b.is_diagnosis,b.landing_times from user_expand as b right join (select id,username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where auth_user.username='{}') as a on b.user_name=a.username""".format(username)

        self.obj.execute(sql)
        query_data = self.obj.fetchall()

        return query_data

    def unbundling(self, username):
        """解绑店铺"""

        sql = """select id from auth_user where username='%s'""" % (username)
        self.obj.execute(sql)
        quer_data = self.obj.fetchone()
        if quer_data:
            id = quer_data[0]
            sql_1 = """delete from taobao_account where user_id=%d""" % id
            self.obj.execute(sql_1)
            self.con.commit()
            return True
        return False

    def activation_vip(self, username, statdate, level):
        """激活 vip"""

        sql = """select id from auth_user where username='%s'""" % (username)
        self.obj.execute(sql)
        quer_data = self.obj.fetchone()
        if quer_data:
            id = quer_data[0]
            sql_2 = """SET FOREIGN_KEY_CHECKS = 0"""
            self.obj.execute(sql_2)
            sql_1 = """update auth_user set vip_date='%s',vip_level='%s' where id=%d""" % (statdate, level, id)
            self.obj.execute(sql_1)
            sql_3 = """SET FOREIGN_KEY_CHECKS = 1"""
            self.obj.execute(sql_3)
            self.con.commit()
            return True
        return False

    def delete_user(self, username):
        """删除用户"""
        sql = """select id from auth_user where username in {}""".format(username)
        self.obj.execute(sql)
        quer_data = self.obj.fetchall()
        if quer_data:
            id_list = [0]
            for i in quer_data:
                id_list.append(i[0])

            id = tuple(id_list)

            sql_2 = """SET FOREIGN_KEY_CHECKS = 0"""
            self.obj.execute(sql_2)
            sql_1 = """delete from auth_user where id in {}""".format(id)
            self.obj.execute(sql_1)
            sql_4 = """delete from taobao_account where user_id in {}""".format(id)
            self.obj.execute(sql_4)
            sql_3 = """SET FOREIGN_KEY_CHECKS = 1"""
            self.obj.execute(sql_3)
            self.con.commit()
            return True
        return False

    def update_user_qq(self, username, qq):
        """添加qq"""
        sql_2 = """SET FOREIGN_KEY_CHECKS = 0"""
        self.obj.execute(sql_2)
        sql_1 = """update auth_user set user_qq={} where username='{}'""".format(qq, username)
        self.obj.execute(sql_1)
        sql_3 = """SET FOREIGN_KEY_CHECKS = 1"""
        self.obj.execute(sql_3)
        self.con.commit()

    def update_user_wx(self, username, wx):
        """添加微信"""
        sql_2 = """SET FOREIGN_KEY_CHECKS = 0"""
        self.obj.execute(sql_2)
        sql_1 = """update auth_user set user_wx='{}' where username='{}'""".format(wx, username)
        self.obj.execute(sql_1)
        sql_3 = """SET FOREIGN_KEY_CHECKS = 1"""
        self.obj.execute(sql_3)
        self.con.commit()

    def __del__(self):
        # print('ok')
        self.obj.close()
        self.con.close()


def Crowd_Portrait(women_variance, age_variance, city_rate_variance):
    """人群画像查询"""
    con, obj = Link_database()
    age18, age25, age30, age35, age40, age50 = age_variance

    women = [float(women_variance)]  # 女性方差列表
    age_18_24 = [float(age18)]  # 18-24 人数方差列表
    age_25_29 = [float(age25)]  # 25-29 人数方差列表
    age_30_34 = [float(age30)]  # 30-34 人数方差列表
    age_35_39 = [float(age35)]  # 35-39 人数方差列表
    age_40_49 = [float(age40)]  # 40-49 人数方差列表
    age_50 = [float(age50)]  # 50 人数方差列表
    city = [float(city_rate_variance)]  # 城市方差

    sql_1 = """select gender_variance from crowd_portrait"""  # 查询性别
    obj.execute(sql_1)
    for i in obj.fetchall():
        women.append(float(i[0].split('-')[0]))

    sql_2 = """select age_variance from crowd_portrait"""  # 查询年龄
    obj.execute(sql_2)
    for n in obj.fetchall():
        age_18_24.append(float(n[0].split('-')[0]))
        age_25_29.append(float(n[0].split('-')[1]))
        age_30_34.append(float(n[0].split('-')[2]))
        age_35_39.append(float(n[0].split('-')[3]))
        age_40_49.append(float(n[0].split('-')[4]))
        age_50.append(float(n[0].split('-')[5]))

    sql_3 = """select city_variance from crowd_portrait"""  # 查询年龄
    obj.execute(sql_3)
    for m in obj.fetchall():
        city_database = m[0].split('-')
        for c in city_database[1::2]:
            city.append(float(c))

    # 排序
    women.sort()
    age_18_24.sort()
    age_25_29.sort()
    age_30_34.sort()
    age_35_39.sort()
    age_40_49.sort()
    age_50.sort()
    city.sort()

    # 参数排序位置得分len(women)
    women_index_score = (1 - (women.index(float(women_variance)) + 1) / len(women)) * 100
    age18_index_score = (1 - (age_18_24.index(float(age18)) + 1) / len(age_18_24)) * 100
    age25_index_score = (1 - (age_25_29.index(float(age25)) + 1) / len(age_25_29)) * 100
    age30_index_score = (1 - (age_30_34.index(float(age30)) + 1) / len(age_30_34)) * 100
    age35_index_score = (1 - (age_35_39.index(float(age35)) + 1) / len(age_35_39)) * 100
    age40_index_score = (1 - (age_40_49.index(float(age40)) + 1) / len(age_40_49)) * 100
    age50_index_score = (1 - (age_50.index(float(age50)) + 1) / len(age_50)) * 100
    city_index_score = (1 - (city.index(float(city_rate_variance)) + 1) / len(city)) * 100

    age_score = (age18_index_score / 6) + (age25_index_score / 6) + (age30_index_score / 6) + (age35_index_score / 6) + (age40_index_score / 6) + (age50_index_score / 6)

    # 分数综合
    num = (women_index_score / 3) + (age_score / 3) + (city_index_score / 3)

    return num


def sel_user_statistical(vip_level, start_time, end_time):
    """查询用户数据"""
    con, obj = Link_database()
    vip_level = str(vip_level)

    sql = """select count(id) from auth_user where vip_level={}""".format(vip_level)
    obj.execute(sql)
    n = obj.fetchone()[0]
    sql_1 = """select count(id) from auth_user where vip_level={} and date_joined between STR_TO_DATE('{}','%Y-%m-%d') and STR_TO_DATE('{}','%Y-%m-%d')""".format(vip_level, start_time, end_time)
    obj.execute(sql_1)
    m = obj.fetchone()[0]
    sql_2 = """select count(id) from auth_user where vip_level={} and vip_date between STR_TO_DATE('{}','%Y-%m-%d') and STR_TO_DATE('{}','%Y-%m-%d')""".format(vip_level, start_time, end_time)
    obj.execute(sql_2)
    u = obj.fetchone()[0]

    start_time = int(time.mktime(time.strptime(start_time, '%Y-%m-%d')))
    end_time = int(time.mktime(time.strptime(end_time, '%Y-%m-%d')))

    sql_3 = """select sum(upgrade_user),sum(sms_contact),sum(phone_contact),sum(problem_feedback),sum(problem_solve),sum(diagnosis) from user_statistical where vip_level={} and statdate between {} and {}""".format(vip_level, start_time, end_time)
    obj.execute(sql_3)
    q = obj.fetchone()

    query = [vip_level, n, m, u]
    for i in q:
        query.append(i)

    obj.close()
    con.close()
    return query


class sel_due_to(object):
    """不同等级到期类用户查询"""
    def __init__(self, page, level):
        self.page = page
        self.level = level
        self.con, self.obj = Link_database()

    def due_to(self):
        """已到期"""
        check = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

        sql_1 = """select count(id) from auth_user where vip_level='%s' and vip_date between '2018-07-18 10:54:58' and '%s'""" % (self.level, check)
        self.obj.execute(sql_1)
        q = self.obj.fetchone()

        # sql = """select username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where vip_level='%s' and vip_date between '2018-07-18 10:54:58' and '%s' order by vip_date desc limit %d,20""" % (
        #     self.level, check, self.page)
        sql = """select a.*, b.zd_nickname,b.zd_passwd,b.pay_date,b.is_diagnosis,b.landing_times from user_expand as b right join (select id,username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where vip_level='%s' and vip_date between '2018-07-18 10:54:58' and '%s' order by vip_date desc limit %d,25) as a on b.user_name=a.username""" % (
            self.level, check, self.page)

        return sql, q

    def this_month(self):
        """本月到期"""
        times = datetime.datetime.now().strftime("%Y-%m")
        years, month = times.split('-')

        day = calendar.monthrange(int(years), int(month))[1]

        start_time = times + "-01 00:00:00"
        end_time = times + "-" + str(day) + " 23:59:59"

        sql_1 = """select count(id) from auth_user where vip_level='%s' and vip_date between '%s' and '%s'""" % (self.level, start_time, end_time)
        self.obj.execute(sql_1)
        q = self.obj.fetchone()

        sql = """select a.*, b.zd_nickname,b.zd_passwd,b.pay_date,b.is_diagnosis,b.landing_times from user_expand as b right join (select id,username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where vip_level='%s' and vip_date between '%s' and '%s' order by vip_date desc limit %d,25) as a on b.user_name=a.username""" % (
            self.level, start_time, end_time, self.page)

        return sql, q

    def next_month(self):
        """下月到期"""
        times = datetime.datetime.now().strftime("%Y-%m")
        years, month = times.split('-')
        month = int(month) + 1
        if month > 12:
            month = 1
            years = int(years) + 1

        day = calendar.monthrange(int(years), int(month))[1]

        start_time = str(years) + "-" + str(month) + "-01 00:00:00"
        end_time = str(years) + "-" + str(month) + "-" + str(day) + " 23:59:59"

        sql_1 = """select count(id) from auth_user where  vip_level='%s' and vip_date between '%s' and '%s'""" % (self.level, start_time, end_time)
        self.obj.execute(sql_1)
        q = self.obj.fetchone()

        sql = """select a.*, b.zd_nickname,b.zd_passwd,b.pay_date,b.is_diagnosis,b.landing_times from user_expand as b right join (select id,username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where vip_level='%s' and vip_date between '%s' and '%s' order by vip_date desc limit %d,25) as a on b.user_name=a.username""" % (
            self.level, start_time, end_time, self.page)

        return sql, q

    def main(self, sql):
        self.obj.execute(sql)
        q = self.obj.fetchall()

        return q

    def __del__(self):
        self.obj.close()
        self.con.close()


class registered_time(object):
    """注册时间查询"""
    def __init__(self, page):
        self.page = page
        self.con, self.obj = Link_database()

    def day_1(self):
        times = datetime.datetime.now().strftime("%Y-%m-%d")
        srart = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime("%Y-%m-%d")

        start_time = srart + " 00:00:00"
        end_time = times + " 23:59:59"

        sql_1 = """select count(id) from auth_user where date_joined between '%s' and '%s'""" % (start_time, end_time)
        self.obj.execute(sql_1)
        q = self.obj.fetchone()

        sql = """select a.*, b.zd_nickname,b.zd_passwd,b.pay_date,b.is_diagnosis,b.landing_times from user_expand as b right join (select id,username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where auth_user.date_joined between '%s' and '%s' order by vip_date desc limit %d,25) as a on b.user_name=a.username""" % (
            start_time, end_time, self.page)

        return sql, q

    def day_3(self):
        times = datetime.datetime.now().strftime("%Y-%m-%d")
        srart = (datetime.datetime.now() + datetime.timedelta(days=-3)).strftime("%Y-%m-%d")

        start_time = srart + " 00:00:00"
        end_time = times + " 23:59:59"

        sql_1 = """select count(id) from auth_user where date_joined between '%s' and '%s'""" % (start_time, end_time)
        self.obj.execute(sql_1)
        q = self.obj.fetchone()

        sql = """select a.*, b.zd_nickname,b.zd_passwd,b.pay_date,b.is_diagnosis,b.landing_times from user_expand as b right join (select id,username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where auth_user.date_joined between '%s' and '%s' order by vip_date desc limit %d,25) as a on b.user_name=a.username""" % (
            start_time, end_time, self.page)

        return sql, q

    def day_7(self):
        times = datetime.datetime.now().strftime("%Y-%m-%d")
        srart = (datetime.datetime.now() + datetime.timedelta(days=-7)).strftime("%Y-%m-%d")

        start_time = srart + " 00:00:00"
        end_time = times + " 23:59:59"

        sql_1 = """select count(id) from auth_user where date_joined between '%s' and '%s'""" % (start_time, end_time)
        self.obj.execute(sql_1)
        q = self.obj.fetchone()

        sql = """select a.*, b.zd_nickname,b.zd_passwd,b.pay_date,b.is_diagnosis,b.landing_times from user_expand as b right join (select id,username,first_name,taobao_account_name,last_login_time,date_joined,vip_date,vip_level,user_qq,user_wx from auth_user left join taobao_account on auth_user.id=taobao_account.user_id where auth_user.date_joined between '%s' and '%s' order by vip_date desc limit %d,25) as a on b.user_name=a.username""" % (
            start_time, end_time, self.page)

        return sql, q

    def main(self, sql):
        self.obj.execute(sql)
        q = self.obj.fetchall()

        return q

    def __del__(self):
        self.obj.close()
        self.con.close()


if __name__ == '__main__':
    start = int(time.mktime(time.strptime('2018-8-17', '%Y-%m-%d')))
    check = int(time.mktime(time.strptime('2018-8-17', '%Y-%m-%d')))
    print(start)
    print(check)
    # a = OeratingTake(1, '旺旺店铺', start, check)
    # query_data = TrafficTake(17, None, start, check)
    # lists = []
    # for i in query_data:
    #     list_1 = []
    #     temporary_dict = {
    #         "WX": {
    #             "statDate": {"value": i[2]},
    #             "pageName": i[3],
    #             "uv": {"value": i[4], "cycleCrc": i[5]},
    #             "crtByrCnt": {"value": i[6], "cycleCrc": i[7]},
    #             "crtRate": {"value": i[8], "cycleCrc": i[9]}
    #             }
    #         }
    #     list_1.append(temporary_dict)
    #     lists.append(list_1)
    #
    # result = {"map": lists}
    # print(result)

    # TrafficTake('table', 17)

    # a = ServiceIndicators(16, '123', start, check)
    # print(a)

    # a = TrafficTake(1, '旺旺', start, check)
    # print(a.select_data())
    # print('-----------------------')
    # print(a.PC_table_data())
    # print('======================')
    # print(a.WX_table_data())
    # a.the_end()

    # a = ItemAnalysis(318, '2348', start, check, 562655396090)
    # # c = (571662022824, 564617538978, 563782979058)
    # c = (562655396090, 0)
    # # print(a.select_data(c), '商品数据')
    # # print(len(a.select_data(c)))
    # print('------------------------')
    # print(a.select_sum_data(c), '时间段数据总和')
    # print('======================================')
    # a.save_associatcd_table()
    # # time.sleep(0.01)
    # # print(a.select_associatcd_table(), '用户商品id')
    # a.the_end()

    # a = ItemKeywords(16, '', start, check, '')
    # print(a.select_data())

    # a = SelectKey(16, '', '', '钢琴 儿童 初学者', start, check)
    # a = When_Month(325, '2018-10')
    # a = Before_Month(325, '彩妆/香水/美妆工具', '指甲彩妆', 1506787200, 1535731200)
    # obj = Admin_after()
    # from urllib import parse
    # c = obj.all_data()
    # a = parse.unquote(c[0][1])

    # print(c)
    n = Crowd_Portrait('0.0', ['0.0', '0.0', '0.0', '0.0', '0.0', '0.0'], '0.0')
    print(n)